Our team delivers OpenPrescribing.net, a publicly funded and openly accessible explorer for NHS primary care prescribing supported by prescribing data openly published by the NHS Business Services Authority.
We were concerned that the NHS did not share hospital medicines data in a similar manner to primary care and that it should be shared (see Goldacre and MacKenna 2020).
In September 2020 NHS BSA published hospital medicines data for the first time. We have prepared the following notebook for investigating the use of antibiotics in English hospitals.
If you have any feedback or insight The DataLab team can be contacted at ebmdatalab@phc.ox.ac.uk.
We will load the csv containing the SNOMED codes for the medicines we are investigating. It also contains relevant groupings and DDDs (where they exist). More information about each SNOMED code included in this analysis is shown in the methods section below (see dm+d Table).
# Define vector with relevant SNOMED codes
codelist = read_csv(here("data/antibac_codelist.csv"),
# convert to strings (they are stored as strings in SCMD table)
col_types = cols(id = col_character()))
# changed col type in read_csv function above
# (read_csv works slightly different to read.csv)
codes = codelist$idBefore we can analyse trends and variation in the use of this group of medicines we need to prepare our dataset. This analysis uses three different sources of data.
The NHS Digital “GP mapping file” which provides STP and region names mapped to STP and region ODS codes, published here.
The NHS Digital “Etr” file that maps trust organisation codes to trust names, STP ODS codes and region ODS codes, published here.
# Load ETR data
df_etr <- readr::read_csv(here::here("data/etr_tidy.csv")) %>%
select(c("ods_code", "ods_name", "region_code", "stp_code"))
# load older ETR data and find any additional codes here not in current list
df_etr_historic <- readr::read_csv(here::here("data/etr.csv"),
col_names = FALSE) %>%
select(1:4)
colnames(df_etr_historic) <- c("ods_code", "ods_name", "region_code", "stp_code")
df_etr_intersect <- intersect(df_etr$ods_code, df_etr_historic$ods_code)
df_etr_historic <- df_etr_historic %>%
dplyr::filter(!ods_code %in% df_etr_intersect)
df_etr <- rbind(df_etr, df_etr_historic)
# Load stp to regions data
stp_to_region_map <- read_csv(here::here("data/gp-reg-pat-prac-map.csv")) %>%
group_by(STP_CODE, STP_NAME) %>%
summarise(COMM_REGION_NAME = first(COMM_REGION_NAME),
COMM_REGION_CODE = first(COMM_REGION_CODE)) %>%
janitor::clean_names()
# check which STPs are in lookup table
stp_count <- df_etr %>%
group_by(stp_code) %>%
summarise(n = n(),
ods_code = first(ods_code),
ods_name = first(ods_name))
stp_count <- left_join(stp_count, stp_to_region_map, by = "stp_code")# Sustainability and Transformation Partnerships (STPs)
df_etr %>%
left_join(stp_to_region_map, by = "stp_code") %>%
select(ods_name, stp_name, comm_region_name) %>%
mutate(stp_name = fct_explicit_na(stp_name),
comm_region_name = fct_explicit_na(comm_region_name)) %>%
reactable::reactable(filterable = TRUE,
columns = list(ods_name = reactable::colDef(name = "Name",
minWidth = 200),
stp_name = reactable::colDef(name = "STP",
minWidth = 150),
comm_region_name = reactable::colDef(name = "Region",
minWidth = 70)),
style = list(fontSize = "12px"),
highlight = TRUE)# Secondary Care Medicines Data
# Connect to database, filter, and collect data.
# Get SCMD dataset
# Check negative quantities, seems to be a small (0.7%) problem in the data
# dplyr::tbl(conn_ebm_scmd, "scmd") %>%
# filter(between(year_month, "2019-01-01", "2019-12-31")) %>%
# # arrange(desc(year_month))
# filter(total_quanity_in_vmp_unit > 0) %>%
# count()
# group_by(vmp_snomed_code) %>%
# summarise(n = n(),
# sum = sum(total_quanity_in_vmp_unit < 0))
db_scmd <- dplyr::tbl(conn_ebm_scmd, sql_query_scmd)
# Create dataframe for table
db_scmd <- db_scmd %>%
dplyr::filter(vmp_snomed_code %in% codes)
db_scmd <- dplyr::collect(db_scmd)
# df_scmd %>% skimr::skim()
# Tidy tidy tidy data
df_scmd_names <- db_scmd %>%
dplyr::left_join(dplyr::select(df_etr, ods_code, ods_name, stp_code), by = "ods_code") %>%
# some data cleaning as scmd uses some ods codes that are not up to date
mutate(stp_code = as.character(stp_code),
stp_code = case_when(
ods_code == "RQ6" ~ "QYG", # cheshire + merseyside
ods_code %in% c("RNL", "RE9", "RLN") ~ "QHM", # cumbria
ods_code %in% c("RM2", "RW3") ~ "QOP", # Mcr
ods_code == "RGQ" ~ "QJG", # Suffolk and North East Essex
ods_code == "RJF" ~ "QJ2", # Derbyshire
ods_code == "RR1" ~ "QHL", # Birmingham
ods_code == "R1J" ~ "QR1", # gloucestershire (trust present in data but wrong/old code)
ods_code == "R1E" ~ "QNC", # Staffs
ods_code == "TAD" ~ "QWO", # W Yorks
ods_code == "TAJ" ~ "QUA", # Black country
ods_code == "TAH" ~ "QF7", # South Yorkshire & Bassetlow
ods_code == "TAF" ~ "QMJ", # North Central London
TRUE ~ stp_code
))
check_missing <- select(df_scmd_names,c("ods_code", "ods_name", "stp_code")) %>%
distinct(.keep_all = TRUE)
check_missing <- check_missing[order(check_missing[["ods_name"]]), ]
# check which STPs are in data
scmd_stp_count <- df_scmd_names %>%
group_by(stp_code) %>%
summarise(n = n(),
ods_code = first(ods_code),
ods_name = first(ods_name))# Fill explicit missing and create dataset for sparkline in table
df_tab_sparkline <- df_scmd_names %>%
select(-c(vmp_product_name, ods_name, stp_code, stp_code, ods_name)) %>%
arrange(ods_code, vmp_snomed_code, year_month) %>%
as_tsibble(key = c(ods_code, vmp_snomed_code), index = year_month) %>%
fill_gaps(total_quantity = 0, .full = TRUE) %>%
tidyr::fill(.direction = "down") %>%
as_tibble() %>%
mutate(year_month = floor_date(year_month, unit = "month")) %>%
group_by(year_month, ods_code, vmp_snomed_code) %>%
arrange(ods_code, vmp_snomed_code, year_month) %>%
mutate(total_quantity = sum(total_quantity)) %>%
arrange(ods_code, vmp_snomed_code, year_month) %>%
distinct() %>%
group_by(ods_code, vmp_snomed_code) %>%
dplyr::summarise(count_sparkline = list(total_quantity)) %>%
group_by(ods_code, vmp_snomed_code) %>%
dplyr::mutate(total_quantity = sum(unlist(count_sparkline)))
# Create lookup datasets for joining
# SNOMED
vmp_snomed_names_lookup <- df_scmd_names %>%
select(vmp_snomed_code, vmp_product_name) %>%
dplyr::distinct()
# Trust
trust_names_lookup <- df_scmd_names %>%
select(ods_code, ods_name, stp_code) %>%
dplyr::distinct()
# Join data
df_tab_sparkline <- df_tab_sparkline %>%
ungroup() %>%
arrange(ods_code) %>%
left_join(trust_names_lookup, by = c("ods_code")) %>%
left_join(vmp_snomed_names_lookup, by = c("vmp_snomed_code")) %>%
mutate(count_box = count_sparkline)
# See the ?tippy documentation to learn how to customize tooltips
with_tooltip <- function(value, tooltip, ...) {
div(style = "text-decoration: underline; text-decoration-style: dotted; cursor: help",
tippy(value, tooltip, ...))
}
# Create table
df_tab_sparkline %>%
select(ods_name, vmp_product_name,vmp_snomed_code, count_sparkline, count_box, total_quantity,
-ods_code, -stp_code) %>%
reactable(filterable = TRUE,
defaultSorted = c("ods_name", "total_quantity"),
groupBy = c("ods_name"),
columns = list(
ods_name = reactable::colDef(name = "Trust",
minWidth = 200),
count_sparkline = colDef(name = "Trend",
header = with_tooltip("Trend", "Note that the y axis cannot be compared across different entries."),
minWidth = 50,
cell = function(value, index) {
sparkline(df_tab_sparkline$count_sparkline[[index]])
}),
count_box = reactable::colDef(show = FALSE),
total_quantity = reactable::colDef(name = "Quantity",
minWidth = 50,
aggregate = "sum",
format = reactable::colFormat(digits = 0)),
vmp_product_name = reactable::colDef(name = "Product",
minWidth = 150,
cell = function(value, index) {
vmp_snomed_code <- paste0("SNOMED: ", df_tab_sparkline$vmp_snomed_code[index])
vmp_snomed_code <- if (!is.na(vmp_snomed_code)) vmp_snomed_code else "Unknown"
div(
div(style = list(fontWeight = 600), value),
div(style = list(fontSize = 10), vmp_snomed_code))
}
),
vmp_snomed_code = reactable::colDef(show = FALSE)
),
style = list(fontSize = "12px"),
highlight = TRUE
)